#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
MySQL 8.0 数据库连接测试
"""
import mysql.connector
from mysql.connector import Error
import logging

logger = logging.getLogger(__name__)


class MySQLDB:
    def __init__(self):
        # MySQL 8.0 连接配置
        self.config = {
            'host': '127.0.0.1',
            'port': 3308,
            'user': 'root',
            'password': 'root',  # 请修改为您的密码
            'database': 'health_boot',  # 请修改为您的数据库名
            'charset': 'utf8mb4',
            'auth_plugin': 'mysql_native_password'  # MySQL 8.0 认证插件
        }
        self.connection = None

    def get_connection(self):
        """获取数据库连接"""
        try:
            if not self.connection or not self.connection.is_connected():
                self.connection = mysql.connector.connect(**self.config)
            return self.connection
        except Error as e:
            logger.error(f"获取数据库连接失败: {e}")
            return None

    def close_connection(self):
        """关闭数据库连接"""
        if self.connection and self.connection.is_connected():
            self.connection.close()
            logger.info("数据库连接已关闭")


    def get_patient_results(self, limit=10):
        """获取患者分组结果"""
        try:
            connection = self.get_connection()
            if not connection:
                return []

            cursor = connection.cursor(dictionary=True)

            query_sql = """
            SELECT * FROM dip_patient_results 
            ORDER BY created_at DESC 
            LIMIT %s
            """

            cursor.execute(query_sql, (limit,))
            results = cursor.fetchall()

            cursor.close()
            return results

        except Error as e:
            logger.error(f"查询患者结果时发生错误: {e}")
            return []

    def get_patient_by_diagnosis(self, diagnosis_code):
        """根据诊断编码查询患者"""
        try:
            connection = self.get_connection()
            if not connection:
                return []

            cursor = connection.cursor(dictionary=True)

            query_sql = """
            SELECT * FROM dip_patient_results 
            WHERE diagnosis_code = %s 
            ORDER BY created_at DESC
            """

            cursor.execute(query_sql, (diagnosis_code,))
            results = cursor.fetchall()

            cursor.close()
            return results

        except Error as e:
            logger.error(f"根据诊断编码查询时发生错误: {e}")
            return []


    def query_test_data(self):
        """查询测试数据"""

        try:
            self.connection = mysql.connector.connect(**self.config)
            cursor = self.connection.cursor()

            cursor.execute("SELECT * FROM dip_patient_results ORDER BY created_at DESC LIMIT 5")
            results = cursor.fetchall()

            logger.info("查询结果:")
            for row in results:
                logger.info(f"ID: {row[0]}, 患者: {row[1]}, 诊断编码: {row[2]}")

            cursor.close()

        except Error as e:
            logger.error(f"查询数据时发生错误: {e}")

        finally:
            if self.connection and self.connection.is_connected():
                self.connection.close()
